﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Diagnostics;
using System.Text;

namespace Rodunu.Utilities.Import
{
    public enum ViewedTypes
    {
        YN = 1,
        YesNo = 2,
        TrueFalse = 3
    }

    public class ImportFromExcel
    {
        #region Properties

        public string File { get; set; }
        public string SheetName { get; set; }

        public string MovieVariable { get; set; }
        public string TypeVariable { get; set; }
        public string ViewedVariable { get; set; }
        public string CollectionVariable { get; set; }
        public string PageVariable { get; set; }

        public ViewedTypes? ViewedType { get; set; }

        public IList<ImportMovie> Movies { get; set; }

        #endregion

        #region Constructors

        public ImportFromExcel()
        {
            Movies = new List<ImportMovie>();
        }

        public ImportFromExcel(string file, string sheet)
        {
            File = file;
            SheetName = sheet;
            Movies = new List<ImportMovie>();
        }

        #endregion
        
        public void Read()
        {
            //c:\Beau's Movies.xls

            var connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=";
            connectionString += File;
            connectionString += @";Extended Properties=""Excel 8.0;HDR=YES;""";

            var factory = DbProviderFactories.GetFactory("System.Data.OleDb");

            StringBuilder commandText = GetCommandText();

            using (DbConnection connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;

                using (DbCommand command = connection.CreateCommand())
                {
                    command.CommandText = commandText.ToString();
                    connection.Open();

                    using (DbDataReader dr = command.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            BuildMovie(dr);
                        }
                    }
                }
            }
        }

        public IList<string> GetExcelSheets(string location)
        {
            var connection = new OleDbConnection(GetConnection(location));
            connection.Open();
            var table = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            IList<string> excelSheets = new List<string>();

            foreach (DataRow row in table.Rows)
            {
                string name = row["TABLE_NAME"].ToString();
                excelSheets.Add(name.Substring(0, name.Length - 1));
            }

            connection.Close();
            return excelSheets;
        }

        private static string GetConnection(string fileLocation)
        {
            return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=Excel 8.0;";
        }

        public IList<string> GetRowHeaders(string sheet, string location)
        {
            IList<string> headers = new List<string>();
            var oleDbCommand = new OleDbCommand("SELECT * FROM " + sheet);
            var objAdapter = new OleDbDataAdapter();
            objAdapter.SelectCommand = oleDbCommand;

            var factory = DbProviderFactories.GetFactory("System.Data.OleDb");
            using (DbConnection connection = factory.CreateConnection())
            {
                connection.ConnectionString = GetConnection(location);
                using (DbCommand command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT TOP 1 * FROM [" + sheet + "$]";
                    connection.Open();

                    using (DbDataReader dr = command.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            for (int i = 0; i < dr.FieldCount; i++)
                            {
                                headers.Add(dr.GetName(i));
                            }

                        }
                    }
                }
            }

            return headers;
        }

        #region Private Methods

        private void BuildMovie(DbDataReader dr)
        {
            var importMovie = new ImportMovie();
            importMovie.MovieName = dr[MovieVariable].ToString();
            if (!string.IsNullOrEmpty(TypeVariable))
                importMovie.Type = dr[TypeVariable].ToString();
            if (!string.IsNullOrEmpty(ViewedVariable))
            {
                SetViewed(dr, importMovie);
            }
            if (!string.IsNullOrEmpty(CollectionVariable))
                importMovie.Collection = dr[CollectionVariable].ToString();
            if (!string.IsNullOrEmpty(PageVariable))
                importMovie.Page = dr[PageVariable].ToString();
            Movies.Add(importMovie);
        }

        private void SetViewed(DbDataReader dr, ImportMovie importMovie)
        {
            if (ViewedType != null)
            {
                switch (ViewedType)
                {
                    case ViewedTypes.YesNo:
                    case ViewedTypes.TrueFalse:
                        try
                        {
                            importMovie.Viewed = Convert.ToBoolean(dr[ViewedVariable]);
                        }
                        catch (Exception)
                        {
                            importMovie.Viewed = null;
                        }
                        break;
                    case ViewedTypes.YN:
                        if (dr[ViewedVariable].ToString().ToUpper() == "Y")
                            importMovie.Viewed = true;
                        else if (dr[ViewedVariable].ToString().ToUpper() == "N")
                            importMovie.Viewed = false;
                        else
                            importMovie.Viewed = null;
                        break;
                    default:
                        importMovie.Viewed = null;
                        break;
                }
            }
            else
                importMovie.Viewed = null;
        }

        private StringBuilder GetCommandText()
        {
            var commandText = new StringBuilder();
            commandText.Append("SELECT ");
            commandText.Append(MovieVariable);
            commandText.Append(AddVariable(TypeVariable, true));
            commandText.Append(AddVariable(ViewedVariable, true));
            commandText.Append(AddVariable(CollectionVariable, true));
            commandText.Append(AddVariable(PageVariable, true));
            commandText.Append(" FROM [");
            commandText.Append(SheetName);
            commandText.Append("$]");
            return commandText;
        }

        #endregion

        #region Static Methods

        private static string AddVariable(string variable, bool withComma)
        {
            if (!string.IsNullOrEmpty(variable))
            {
                if (withComma)
                    return ", " + variable;
                return variable;
            }
            return string.Empty;
        }

        #endregion

        
    }
}
